通过数据库三大范式的学习,我们已经可以确定了需要建立哪些表,每个表中有哪些字段。 但是这些字段的长度和类型如何确定呢？如何选择主键呢?

MySQL 中有很多字段类型，比如整数、文本、浮点数，等等。如果类型定义合理，就能节省存储空间，提升数据查询和处理的速度，相反，如果数据类型定义不合理，就有可能会导致数据超出取值范围，引发系统报错，甚至可能会出现计算错误的情况，进而影响到整个系统。

整数类型一共有5种，包括 TINYINT、SMALLINT、MEDIUMINT、INT（INTEGER）和 BIGINT

一个字节由8个二进制位组成,所以1个字节最大能表示( 2^8-1) 即 256-1=255

同理8个字节由64个二进制位组成,所以1个字节最大能表示(2^64-1)

我们大家都喜欢在建表时指定类型的大小，但是这个大小并不是数据类型的大小，而是宽度，这个数字无论是大是小占用的存储都是一样的，无论是int类型还是varchar类型。

插入数据

int类型有zerofill做填充的时候,才会起作用。

插入数据：

报错：

解释下varchar类型指定宽度的作用：

VARCHAR(N)，N表示的是字符数不是字节数，比如VARCHAR(255)，可以最大可存储255个汉字，需要根据实际的宽度来选择N，MySQL一个表中所有的VARCHAR字段最大长度是65535个字节，进行排序和创建临时表一类的内存操作时，会使用N的长度申请内存

VARCHAR(N)，如果N=256则使用两个字节来存储长度

在评估用哪种整数类型的时候，需要考虑存储空间和可靠性的平衡问题：

占用字节数少的整数类型可以节省存储空间；

要是为了节省存储空间，使用的整数类型取值范围太小，一旦遇到超出取值范围的情况，就可能引起系统错误，影响可靠性。

在实际工作中，系统故障产生的成本远远超过增加几个字段存储空间所产生的成本。因此，首先确保数据不会超过取值范围，在这个前提之下，再去考虑如何节省存储空间。如果因为节省存储空间,导致了系统故障,那就得不偿失了。

年龄的数值范围一般在1-200之间，选择太大的大整数值肯定也可以，但是会造成空间的浪费。对于年龄来说使用数值类型TINYINT就很合适。因为首先在年龄中不会存在负数，因此可以采用TINYINT类型的无符号范围。

这个数据类型虽然是小整数值，但是范围却是0 ~255之间，那么使用它来计算年龄是完全足够了。

MySQL 支持的浮点数类型，分别是 FLOAT、DOUBLE、REAL。

FLOAT 表示单精度浮点数；

DOUBLE 表示双精度浮点数；

REAL 默认就是 DOUBLE。如果把 SQL 模式设定为启用 REAL_AS_FLOAT，那么，MySQL 就认为 REAL 是 FLOAT。如果要启用“REAL_AS_FLOAT”，就可以通过以下 SQL 语句实现：

FLOAT 占用字节数少，取值范围小；DOUBLE 占用字节数多，取值范围也大。浮点数类型的无符号数取值范围，只相当于有符号数取值范围的一半，也就是只相当于有符号数取值范围大于等于零的部分。

浮点数类型有个缺陷，就是不精准。因此，在一些对精确度要求较高的项目中，千万不要使用浮点数，不然会导致结果错误，甚至是造成不可挽回的损失。

会存在这样的误差，问题还是出在 MySQL 对浮点类型数据的存储方式上。

MySQL 用 4 个字节存储 FLOAT 类型数据，用 8 个字节来存储 DOUBLE 类型数据。 无论哪个，都是采用二进制的方式来进行存储的。比如 9.625，用二进制来表达，就是 1001.101，或者表达成 1.001101×2^3。如果尾数不是 0 或 5（比如 9.624），就无法用一个二进制数来精确表达。怎么办呢？就只好在取值允许的范围内进行近似（四舍五入）。（十进制小数转化为二进制小数）

数据类型是 DOUBLE 的时候，得到的结果误差更小一些，而数据类型是 FLOAT 的时候，误差会更大一下。原因就是，DOUBLE 有 8 位字节，精度更高。

定点数 DECIMAL 的存储方式决定了它一定是精准的。

浮点数类型是把十进制数转换成二进制数存储，DECIMAL 则不同，它是把十进制数的整数部分和小数部分拆开，分别转换成十六进制数，进行存储。这样，所有的数值，就都可以精准表达了，不会存在因为无法表达而损失精度的问题。

由于 DECIMAL 数据类型的精准性，在项目中，除了极少数（比如商品编号）用到整数类型外，其他的数值都用的是 DECIMAL，原因就是这个项目所处的零售行业，要求精准，一分钱也不能差。

小结下浮点数和定点数的特点：

浮点类型取值范围大，但是不精准，适用于需要取值范围大，又可以容忍微小误差的科学计算场景（比如计算化学、分子建模、流体动力学等）； 定点数类型取值范围相对小，但是精准，没有误差，适合于对精度要求极高的场景（比如涉及金额计算的场景）。

小数类型可以使用DOUBLE和DECIMAL两者都可，没有太大的区别，要考虑分数不会超过一千分，因此小数点中左边最大只会有3个，分数中包含小数，但是都是.5之类的，因此小数点右边只会有1个，根据分析得知，精度为4，标度为1。

代表的含义是一共有4位,其中1位用来标识小数,其余3位用来标识整数部分。

CHAR和VARCHAR的区别 无论使用CHAR还是VARCHAR都需要指定字符串的长度，例如char(M) varchar(M)。

CHAR(M)：固定长度字符串。CHAR(M) 类型必须预先定义字符串长度。如果太短，数据可能会超出范围导致报错；如果太长，又浪费存储空间。

VARCHAR(M)： 可变长度字符串。VARCHAR(M) 也需要预先知道字符串的最大长度，不过只要不超过这个最大长度，具体存储的时候，是按照实际字符串长度存储的

CHAR是定长字符串，当我们指定存储字符串的长度为10，如果写入的文本字符串的数量不够10个，则会以空格进行填充，性能比VARCHAR好。

VARCHAR是变长字符串，当我们指定存储字符串的长度为10，写入了几个文本字符串就算几个，不会用空格进行补充，由于需要计算写入的字符串数量与总长度进行比较，因此VARCHAR的性能略低于CHAR。

当明确指定该字段写入的字符串数量，并且一定会写入指定数量的字符串时，选择CHAR作为数据类型。

当不固定用户会写入多少个字符串时，但是由文字数量限制，此时就使用VARCHAR作为数据类型。

例如用户名是无法固定的，采用VARCHAR作为数据类型，而性别是固定的，只有一个字符串，则使用CHAR数据类型。

TEXT 类型也有 4 种，它们的区别就是最大长度不同。

TINYTEXT：255 字符（这里假设字符是 ASCII 码，一个字符占用一个字节，下同）

TEXT： 65535 字符

MEDIUMTEXT：16777215 字符

LONGTEXT： 4294967295 字符（相当于 4GB）

需要注意的是，TEXT 也有一个问题：由于实际存储的长度不确定，MySQL 不允许 TEXT 类型的字段做主键。遇到这种情况，只能采用 CHAR(M)，或者 VARCHAR(M)。在项目中，只要不是主键字段，就可以按照数据可能的最大长度，选择这几种 TEXT 类型中的的一种，作为存储字符串的数据类型。

尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

用得最多的日期时间类型，就是 DATETIME。虽然 MySQL 也支持 YEAR（年）、TIME（时间）、DATE（日期），以及 TIMESTAMP 类型，不同数据类型表示的时间内容不同、取值范围不同，而且占用的字节数也不一样，要根据实际需要灵活选取。

常见的日期时间的数据类型有：DATE、TIME、YEAR、DATETIME、TIMESTAMP等，其中最为常用的是DATE、TIME和DATATIME这三种。

DATE类型

TIME类型

YEAR类型

DATETIME类型

TIMESTAMP类型

**当字段为入职时间、毕业时间、出生时间等等，像这种只能固定到年月日的字段，使用DATE数据类型。

像创建时间、更新时间、加入时间等等可以追踪的字段，使用DATETIME类型。

1.两者都可用来表示YYYY-MM-DD HH:MM:SS[.fraction]类型的日期

2.存储方式不一样

对于TIMESTAMP，它把客户端插入的时间从当前时区转化为UTC（世界标准时间）进行存储。查询时，将其又转化为客户端当前时区进行返回。

对于DATETIME，不做任何改变，基本上是原样输入和输出。

3.两者所能存储的时间范围不一样

timestamp所能存储的时间范围为：'1970-01-01 00:00:01.000000' 到 '2038-01-19 03:14:07.999999'。

datetime所能存储的时间范围为：'1000-01-01 00:00:00.000000' 到 '9999-12-31 23:59:59.999999'。

4.占用空间大小不一样:TIMESTAMP占用4字节,DATETIME占用8字节。

总结：TIMESTAMP和DATETIME除了存储范围和存储方式以及占用空间不一样，没有太大区别。当然，对于跨时区的业务，TIMESTAMP更为合适。

参考:dev.mysql.com/doc/refman/… 参考:www.cnblogs.com/ivictor/p/5… 参考:blog.csdn.net/qq_38278799… 参考:juejin.cn/post/716758…